import pymysql
# task_db_info = {
#     'host': '192.168.16.223',
#     'port': 3306,
#     'user': 'root',
#     'password': 'zhxg_140101',
#     'db': 'zhongbao',
#     'charset': 'utf8mb4',
#     'cursorclass': pymysql.cursors.DictCursor
# }
# task_table = "zhongbao_tasks_new"
task_db_info = {
    'host': '192.168.30.1',
    'port': 3306,
    'user': 'root',
    'password': 'duamiawen&&&&',
    'db': 'DJANGO_WEB_DOMAIN',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.DictCursor
}
task_table = "project_domain_relation"

class ToObject():
    def __init__(self, args):
        self.items = []
        self.results = []
        self.length = 0
        if type(args) == list:
            for kwarg in args:
                if kwarg:
                    self.length += 1
                    self.items.append(ToObject(kwarg))
                    self.results.append(kwarg)
        elif type(args) == dict:
            if args:
                self.length = 1
                self.results = args
                for key, value in args.items():
                    self.__setattr__(key, value)
        else:
            raise TypeError("参数必须是单独的字典，或者字典的列表。")

    def count(self):
        return self.length

    def extract(self):
        return self.results

    def objects(self):
        return self.items

class myModel():
    def __init__(self, db_info, db_table):
        self.db_info = db_info
        self.db_table = db_table

    def all(self):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        sql = "select * from {} where project_id > 0".format(self.db_table)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def allocated(self):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        #sql = "select * from {} where project_id > 0 and allocation_flag is not null".format(self.db_table)
        sql = "select * from {} where allocation_flag is not null and uid is not null".format(self.db_table)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def unallocated(self):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        #sql = "select * from {} where project_id > 0 and allocation_flag is null".format(self.db_table)
        sql = "select * from {} where allocation_flag is null and uid is null".format(self.db_table)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def filter(self, **kwargs):
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        tasks = []
        #sql = "select * from {} where project_id > 0 and allocation_flag <> null".format(self.db_table)
        sql = "select * from {} where project_id > 0".format(self.db_table)
        for key, value in kwargs.items():
            if value == None:
                value = 'null'
            sql += " and {} = {}".format(key, value)
        try:
            cur.execute(sql)
            tasks = list(cur.fetchall())
        except Exception as e:
            pass
        finally:
            conn.close()
        return tasks

    def get(self, **kwargs):
        if not kwargs:
            raise ValueError("0 kwargs was given.")
        length = len(kwargs)
        if length > 1:
            raise ValueError("Only need 1 kwargs, but too many were given.")
        tasks = self.filter(**kwargs)
        if len(tasks) > 1:
            raise ValueError("Results not single.")
        elif len(tasks) == 0:
            return None
        else:
            return tasks[0]

    def update(self,id,**kwargs):
        if not kwargs:
            raise ValueError("0 kwargs was given.")
        keyValues = []
        for key, value in kwargs.items():
            keyValues.append("{}={}".format(key,value))
        sql = "update {} set ".format(self.db_table)
        _sql = ",".join(keyValues)
        sql = sql + _sql + " where id={}".format(id)
        conn = pymysql.connect(**(self.db_info))
        cur = conn.cursor()
        try:
            cur.execute(sql)
            conn.commit()
            return True
        except Exception as e:
            return False
        finally:
            conn.close()


MyModel = myModel(task_db_info, task_table)
# TaskModel.objects.all()
# kwargs = {
#     #"site_type": 1,
#     #"allocation_flag": None
# }
# mm = MyModel(task_db_info, task_table)
#tasks = mm.filter(**kwargs).extract()
#print(tasks)
# tasks = mm.filter(**kwargs).objects()
# for task in tasks:
#     print(task.site_url)
# tasks = mm.all().objects()
# for task in tasks:
#     print(task.site_url)
# task = mm.get(site_type=1)
# print(task.extract())